/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.Properties;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPBankAccount;
import org.compiere.model.Mhrmbonus;
import org.compiere.model.Mhrmbusinesstrip;
import org.compiere.model.Mhrmmaterialaid;
import org.compiere.model.Mhrmpayment;
import org.compiere.model.Mhrmreview;
import org.compiere.model.Mhrmstatement;
import org.compiere.model.Mhrmstatementline;
import org.compiere.model.Mhrmvacation;
import org.compiere.model.X_C_BP_BankAccount;
import org.compiere.model.X_C_Bank;
import org.compiere.model.X_hrm_statement;
import org.compiere.process.ProcessInfo;
import org.compiere.process.SvrProcess;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class ReportPayrollPayments extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private String path = "";
	/** */
	Mhrmstatement statement = null;
	/** */
	Timestamp dateFrom;
	/** */
	Timestamp datePayment = new Timestamp(new org.joda.time.DateTime().getMillis());
	/** */
	Mhrmbonus bonus = null;
	/** */
	Mhrmbusinesstrip btrip = null;
	/** */
	Mhrmmaterialaid aid = null;
	/** */
	Mhrmvacation vacation = null;
	/** */
	Mhrmreview review = null;	
	/** */
	Mhrmpayment payment = null;

	@Override
	protected void prepare() {
		
		log.info(" Report Payroll Payments ");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		statement = new Mhrmstatement(m_ctx, getRecord_ID(), get_TrxName());
		if(statement == null || getRecord_ID() == 0)
			throw new AdempiereException("Bad Statement or Record_ID is null");
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
		
		dateFrom = statement.getDateFrom();
	}

	@Override
	protected String doIt() throws Exception {
		
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "SamplePayrollPayments";
		// Title
		String title = "";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Payroll Payments").append(fileExtension);
		
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableFont standartFont = new WritableFont (WritableFont.ARIAL, 8, WritableFont.NO_BOLD);
		WritableCellFormat numberDouble = new WritableCellFormat(standartFont, NumberFormats.THOUSANDS_FLOAT);
		numberDouble.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		numberDouble.setAlignment(Alignment.RIGHT);
		numberDouble.setVerticalAlignment(VerticalAlignment.CENTRE);
		numberDouble.setWrap(true);
		// Department style cell
		WritableFont fontInitial = new WritableFont (WritableFont.ARIAL, 9, WritableFont.NO_BOLD);
		WritableCellFormat cellInitial = new WritableCellFormat(fontInitial);
		cellInitial.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellInitial.setAlignment(Alignment.LEFT);
		cellInitial.setVerticalAlignment(VerticalAlignment.CENTRE);
		// BPartner style cell
		WritableFont fontLabel = new WritableFont (WritableFont.ARIAL, 8, WritableFont.NO_BOLD);
		WritableCellFormat labelStyle = new WritableCellFormat(fontLabel);
		labelStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		labelStyle.setAlignment(Alignment.CENTRE);
		labelStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		labelStyle.setWrap(true);
		
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents()+Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
		
		// title
		if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_BONUS)){
			title = " премии ";
			bonus = new Mhrmbonus(m_ctx, statement.gethrm_bonus_ID(), get_TrxName());
			datePayment = bonus.getDateFrom();
		}
		else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_BUSINESSTRIP)){
			title = " командировочных ";
			btrip = new Mhrmbusinesstrip(m_ctx, statement.gethrm_businesstrip_ID(), get_TrxName());
			datePayment = btrip.getDateFrom();
		}
		else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_MATERIALAID)){
			title = " материальной помощи ";
			aid = new Mhrmmaterialaid(m_ctx, statement.gethrm_materialaid_ID(), get_TrxName());
			datePayment = aid.getDateFrom();
		}
		else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_REVIEW)){
			title = " компенсации ";
			review = new Mhrmreview(m_ctx, statement.gethrm_review_ID(), get_TrxName());
			datePayment = review.getDateFrom();
		}
		else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_VACATION)){
			title = " отпускных ";
			vacation = new Mhrmvacation(m_ctx, statement.gethrm_vacation_ID(), get_TrxName());
			datePayment = vacation.getDateFrom();
		}
		else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_COTCH)
				|| statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_CNALOG)
				|| statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_OPV)
				|| statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_IPN)
				|| statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_SALARY)){
			
			if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_COTCH))
				title = " обязательных социальных отчислений ";
			else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_CNALOG))
				title = " соц. налога ";
			else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_OPV))
				title = " 10% обязательных пенсионных взносов ";
			else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_IPN))
				title = " индивидуального подоходного налога ";
			else if(statement.gethrm_typeOperation().equals(X_hrm_statement.HRM_TYPEOPERATION_SALARY))
				title = " заработной платы ";
				
			payment = new Mhrmpayment(m_ctx, statement.gethrm_payment_ID(), get_TrxName());
			datePayment = payment.getDateFrom();
		}
		
		sheet.setRowView(1, 600);
		
		Object[] param = new Object[]{new org.joda.time.DateTime(dateFrom.getTime()).getDayOfMonth(), Util.getMonthName(new Timestamp(dateFrom.getTime()), Env.getAD_Language(getCtx()), true), 
				  Integer.toString(new org.joda.time.DateTime(dateFrom.getTime()).getYear()), Util.getMonthName(new Timestamp(datePayment.getTime()), Env.getAD_Language(getCtx()), false)};
		readCell = sheet.getWritableCell(1, 3);
		readFormat = readCell.getCellFormat();
		label = new Label(1, 3, sheet.getCell(1, 3).getContents()+statement.getValue()+MessageFormat.format(" от {0} {1} {2} года", param), readFormat);
		sheet.addCell(label);
		readCell = sheet.getWritableCell(1, 4);
		readFormat = readCell.getCellFormat();
		X_C_Bank bank = new X_C_Bank(getCtx(), statement.gethrm_settingsPayments().getC_Bank_ID(), get_TrxName());
		label = new Label(1, 4, sheet.getCell(1, 4).getContents() + title + " в " + bank.getName(), readFormat);
		sheet.addCell(label);
		readCell = sheet.getWritableCell(1, 4);
		readFormat = readCell.getCellFormat();
		label = new Label(1, 5, MessageFormat.format("за {3} {2}", param), readFormat);
		sheet.addCell(label);

		Mhrmstatementline[] lines = statement.getLines();
		int col = cellStart.getC(); // offset by the number of first columns
		int row = cellStart.getR();
		// Clear start cells
    	label = new Label(cellStart.getC(), cellStart.getR(), ""); 
    	sheet.addCell(label);
		MBPBankAccount card = new MBPBankAccount(m_ctx, 0, get_TrxName());
		int num = 1;
		int i = 0;
		int n = row;
		double total = 0.;
		for(int j = row; j < (row+lines.length); j++){
			MBPBankAccount[] bpBank = MBPBankAccount.getOfBPartner(m_ctx, lines[i].getC_BPartner_ID());
			for(MBPBankAccount c: bpBank){
				if(statement.gethrm_settingsPayments().getC_Bank_ID() == c.getC_Bank_ID() 
						&& c.getBankAccountType().equals(X_C_BP_BankAccount.BANKACCOUNTTYPE_Checking)){
					card = c;
					break;
				}
			}
			
	    	// shift the basement
		    sheet.insertRow(j);
			
			// #
	    	number = new Number(cellStart.getC(), j, num, labelStyle); 
	    	sheet.addCell(number);
	    	// Initial
	    	label = new Label(col+1, j, lines[i].getC_BPartner().getLastName()+" "+lines[i].getC_BPartner().getFirstName().substring(0,1)+". "+lines[i].getC_BPartner().getMiddleName().substring(0,1)+".", cellInitial); 
	    	sheet.addCell(label);
			// RNN
	    	label = new Label(col+2, j, lines[i].getC_BPartner().getFI_TIN(), labelStyle); 
	    	sheet.addCell(label);
	    	// Card Number
	    	label = new Label(col+3, j, card.getAccountNo(), labelStyle); 
	    	sheet.addCell(label);
	    	// Amount
	    	number = new Number(col+4, j, lines[i].getAmount().doubleValue(), numberDouble); 
	    	sheet.addCell(number);
	    	
	    	total = total+lines[i].getAmount().doubleValue();
	    	
	    	n++;
			num++;
			i++;
		}
		
		readCell = sheet.getWritableCell(1, n);
		readFormat = readCell.getCellFormat();
		// Total Amount
    	number = new Number(col+4, n, total, readFormat); 
    	sheet.addCell(number);
		
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();
			       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return Msg.translate(getCtx(), "Success");
	}

}
